package cn.yunyichina.provider.drug.service.impl;

import cn.yunyichina.provider.drug.dao.DrugDao;
import cn.yunyichina.provider.drug.entity.Drug;
import cn.yunyichina.provider.framework.common.PKGenerator;
import cn.yunyichina.provider.iface.entity.common.Response;
import cn.yunyichina.utils.commons.DateUtils;
import cn.yunyichina.utils.convert.JsonUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.InputStream;
import java.net.HttpURLConnection;
import java.net.URL;
import java.net.URLConnection;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by Administrator on 2017/7/21.
 */
@Service
public class DrugServiceImpl {

    private Logger logger = LoggerFactory.getLogger(DrugServiceImpl.class);

    @Autowired
    private DrugDao dao;

    /**
     * 药品上传(上传医院药品信息，提供药店获取)
     * yyt.drug.drug.excel.import
     * @param data
     * @param sessionId
     * @return
     */
    public Response importDrugExcel(String data, String sessionId) {
        logger.info("【drug模块-药品目录上传】开始, 请求="+data,",sessionId=" + sessionId);
        Response response = new Response();

        Map<String,String> vo = JsonUtils.parseObject(data,Map.class);
        String remoteFile = vo.get("url");//"http://opgl8aq15.bkt.clouddn.com/%E5%8C%97%E4%BA%AC%E5%A4%A7%E5%AD%A6%E6%B7%B1%E5%9C%B3%E5%8C%BB%E9%99%A2_%E6%94%B6%E5%8D%95%E6%9C%BA%E6%9E%84%E8%AE%A2%E5%8D%95%E6%B5%81%E6%B0%B4%E6%98%8E%E7%BB%86.csv?attname=&e=1500614221&token=6MpX59besahQ1TEFbRRd0hGEr8Alf1OMWP9Vr9Pp:cKAzLSXP9sdAvqmH1P0nfnRNEp0";
        logger.info("【drug模块-药品上传】导入excel:url =" + remoteFile);
        Map<String,Object> map = readExcelFile(remoteFile);
        if(map.containsKey("error")){
            response.setResultCodeAndMessage("90002", (String) map.get("error"));
            return response;
        }

        List<Drug> drugList = new ArrayList<>();
        if(map != null && map.size()>0){
            String createTime =  DateUtils.dateToString(new Date());
            for(Map.Entry<String,Object> entry:map.entrySet()){
                if(entry.getValue() instanceof Drug) {
                    Drug drug = (Drug) entry.getValue();
                    drug.setCreatedTime(createTime);
                    drugList.add(drug);
                }
            }
        }
        if(drugList.size() > 0) {
            dao.batchInsert(drugList);
            List<Map<String, Object>> ids = dao.getDuplicateIds();
            List<String> idList = new ArrayList<>();
            for (Map<String, Object> id : ids) {
                idList.add(id.get("ID").toString());
            }
            if(idList != null && idList.size() > 0){
                dao.deleteByIds(idList);
            }
        }
        logger.info("【drug模块-药品上传】导入excel:成功导入" + drugList.size() +"条数据");
        response.setResultCodeAndMessage("0","成功导入" + drugList.size() +"条数据。" + map.get("tips"));
        return response;
    }

    public  Map<String,Object> readExcelFile(String remoteFilePath){
        InputStream is = null;
        Workbook wb = null;
        HashMap<String, Object> map=new HashMap();
        String tips = "";
        try {
            URL urlfile = new URL(remoteFilePath);
            HttpURLConnection httpUrl = (HttpURLConnection)urlfile.openConnection();

            URL url = new URL(remoteFilePath);
            URLConnection conn = url.openConnection();
            conn.setConnectTimeout(3000);
            conn.setReadTimeout(3 * 60 * 1000);
            is = conn.getInputStream();
            wb = WorkbookFactory.create(is);
            Sheet sheet = wb.getSheetAt(0);

            int row_num=sheet.getLastRowNum();
            for(int i=1 ;i<= row_num ;i++){
                Row row=sheet.getRow(i);

                String drugCode = readValue(row.getCell(0));
                String socialInsuranceId = readValue(row.getCell(1));
                String businessName = readValue(row.getCell(2));
                String commonName = readValue(row.getCell(3));
                String unit = readValue(row.getCell(4));
                String dosage = readValue(row.getCell(5));
                String price = readValue(row.getCell(6));
                String approvalNo = readValue(row.getCell(7));
                String drugUsage = readValue(row.getCell(8));
                String frequency = readValue(row.getCell(9));
                String stock = readValue(row.getCell(10));
                String drugFlag = readValue(row.getCell(11));
                String company = readValue(row.getCell(12));
                String hospitalId = readValue(row.getCell(13));
                String hospitalCode = readValue(row.getCell(14));
                String hospitalName = readValue(row.getCell(15));
                String drugUnitNum = readValue(row.getCell(16));
                String drugUnitLimit = readValue(row.getCell(17));
                String drugUnitMax = readValue(row.getCell(18));
                String drugType = readValue(row.getCell(19));

                if(StringUtils.isBlank(drugCode)){
                    logger.info("第" + i +"行<药品代码>不能为空");
                    tips += "第" + i +"行<药品代码>不能为空;";
                    continue;
                }
                if(StringUtils.isBlank(hospitalCode)){
                    logger.info("第" + i +"行<医院编码>不能为空");
                    tips += "第" + i +"行<医院编码>不能为空;";
                    continue;
                }
                if(StringUtils.isBlank(hospitalName)){
                    logger.info("第" + i +"行<医院名称>不能为空");
                    tips += "第" + i +"行<医院名称>不能为空;";
                    continue;
                }
                if(StringUtils.isNotBlank(drugFlag)){
                    Integer flag = Integer.valueOf(drugFlag);
                    if(!(flag==0?true:flag==1?true:flag==2?true:false)){
                        logger.info("第" + i +"行<处方药标志>数据格式非法");
                        tips += "第" + i +"行<处方药标志>数据格式非法;";
                        continue;
                    }
                }
                if(StringUtils.isNotBlank(drugType)){
                    Integer type = Integer.valueOf(drugType);
                    if(!(type==0?true:type==1?true:type==2?true:type==3?true:false)){
                        logger.info("第" + i +"行<药品类型>数据格式非法");
                        tips += "第" + i +"行<药品类型>数据格式非法;";
                        continue;
                    }
                }

                String key = drugCode + hospitalCode + unit;

                if(map.containsKey(key)){
                    logger.info("导入的模板中有重复的<药品代码+医院编码+规格>数据，请检查:药品代码="+ drugCode +",医院编码=" + hospitalCode +",规格=" + unit);
                    tips += "导入的模板中有重复的<药品代码+医院编码+规格>数据，请检查:药品代码="+ drugCode +",医院编码=" + hospitalCode +",规格=" + unit;
                    continue;
                }

                Drug drug = new Drug();
                drug.setId(PKGenerator.generateId());
                drug.setDrugCode(drugCode);
                drug.setSocialInsuranceId(socialInsuranceId);
                drug.setBusinessName(businessName);
                drug.setCommonName(commonName);
                drug.setUnit(unit);
                drug.setDosage(dosage);
                if(StringUtils.isNotBlank(price)){
                    drug.setPrice(Integer.parseInt(price.trim()));
                }
                drug.setApprovalNo(approvalNo);
                drug.setDrugUsage(drugUsage);
                drug.setFrequency(frequency);
                if(StringUtils.isNotBlank(stock)){
                    drug.setStock(Integer.parseInt(stock.trim()));
                }
                if(StringUtils.isNotBlank(drugFlag)){
                    drug.setDrugFlag(Short.parseShort(drugFlag));
                }
                drug.setCompany(company);
                drug.setOrgId(hospitalId);
                drug.setOrgCode(hospitalCode);
                drug.setOrgName(hospitalName);
                if(StringUtils.isNotBlank(drugUnitNum)){
                    drug.setDrugUnitNum(Integer.parseInt(drugUnitNum.trim()));
                }
                drug.setDrugUnitLimit(drugUnitLimit);
                drug.setDrugUnitMax(drugUnitMax);
                if(StringUtils.isNotBlank(drugType)){
                    drug.setDrugType(Short.parseShort(drugType));
                }

                map.put(key,drug);
            }
            map.put("tips",tips);
            return map;

        } catch (Exception e) {
            e.printStackTrace();
            map.put("error","导入发生异常，请检查excel格式是否跟模板一致");

        }finally{

        }
        return map;
    }



    public static String readValue(Cell cell) {
        String value = "";
        if (cell == null) {
            return "";
        }

        int rowType = cell.getCellType();
        if (rowType == Cell.CELL_TYPE_STRING) {
            value = cell.getStringCellValue();

        } else if (rowType == Cell.CELL_TYPE_NUMERIC) {
            if (HSSFDateUtil.isCellDateFormatted(cell)) {

                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                String date = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
                value = date.toString();

            } else {
                DecimalFormat df = new DecimalFormat("0");
                value = df.format(cell.getNumericCellValue());
            }

        }


        return value;
    }

    /**
     * 获取合并单元格的值
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    public static String getMergedRegionValue(Sheet sheet ,int row , int column){
        int sheetMergeCount = sheet.getNumMergedRegions();

        for(int i = 0 ; i < sheetMergeCount ; i++){
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();

            if(row >= firstRow && row <= lastRow){

                if(column >= firstColumn && column <= lastColumn){
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);

                    return getCellValue(fCell) ;
                }
            }
        }

        return null ;
    }

    /**
     * 获取单元格的值
     * @param cell
     * @return
     */
    public static String getCellValue(Cell cell){

        if(cell == null) return "";

        if(cell.getCellType() == Cell.CELL_TYPE_STRING){

            return cell.getStringCellValue();

        }else if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){

            return String.valueOf(cell.getBooleanCellValue());

        }else if(cell.getCellType() == Cell.CELL_TYPE_FORMULA){

            return cell.getCellFormula() ;

        }else if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){

            return String.valueOf(cell.getNumericCellValue());

        }

        return "";
    }

}
